package cn.edu.scut.suggestion.corpus;

/**
 * 用于对数据库进行访问
 * @author tian.yuchen
 */
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.apache.lucene.index.Term;


public class MysqlHelper {
	Connection conn;
	public MysqlHelper() {
		String url="jdbc:mysql://localhost:13306/query_suggestion?useUnicode=true&characterEncoding=UTF8";
		try {
			conn=DriverManager.getConnection(url, "root", "scutccnl");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	protected void finalize() throws Throwable {
		// TODO Auto-generated method stub
		super.finalize();
		conn.close();
	}
	
	
	public int insert(String text,int docfreq,double normdocfreq,double idf,long filepos){
		String sql="Insert term_table(text,docfreq,normdocfreq,idf,filepos) values(?,?,?,?,?)";
		try {
			PreparedStatement prest=conn.prepareStatement(sql);
			prest.setString(1, text);
			prest.setInt(2, docfreq);
			prest.setDouble(3, normdocfreq);
			prest.setDouble(4, idf);
			prest.setLong(5, filepos);
			
			if(prest.execute()){
				conn.commit();
				return 0;
			}else {
				return -1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}

	public TermInfo getTermByText(String text){
		String sql="select text,docfreq,normdocfreq,idf,filepos from term_table where text=?";
		TermInfo termInfo;
		try {
			PreparedStatement prest=conn.prepareStatement(sql);
			prest.setString(1, text);
			ResultSet rs=prest.executeQuery();
			if(rs.next()){
				termInfo=new TermInfo(new Term("content", rs.getString(1)), rs.getInt(2), rs.getDouble(3), rs.getDouble(4), rs.getInt(5));
				return termInfo;
			}else {
				return null;
			}
					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	
	
	public int clearDB(){
		String sql="delete from term_table";
		try {
			PreparedStatement prest=conn.prepareStatement(sql);
			if(prest.execute()){
				conn.commit();
				return 0;
			}else {
				return -1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}
	public TermInfo getTermWithDocsByText(String text,File file){
		TermInfo termInfo=getTermByText(text);
		if(termInfo==null) return null;
		FileReader fReader=new FileReader(file);
		termInfo.docs=fReader.read(termInfo.pos, termInfo.docFreq);
		return termInfo;
	}
	
	public Vector<TermInfo> getCandidatesWithDBByText(String text,File file){
		Vector<TermInfo> resv=new Vector<TermInfo>();
		String sql="select text,docfreq,normdocfreq,idf,filepos from term_table where text like '"+text+"%'";
		PreparedStatement prest;
		try {
			prest = conn.prepareStatement(sql);
			ResultSet rs=prest.executeQuery();
			
			while(rs.next()){
				TermInfo termInfo=new TermInfo(new Term("content", rs.getString(1)), rs.getInt(2), rs.getDouble(3), rs.getDouble(4), rs.getInt(5));
				FileReader fReader=new FileReader(file);
				termInfo.docs=fReader.read(termInfo.pos, termInfo.docFreq);
				resv.add(termInfo);
			}
			if(resv.size()>0){
				return resv;
			}else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static void main(String[] args){
		MysqlHelper mysqlHelper=new MysqlHelper();
		//Vector<TermInfo> tInfos=mysqlHelper.getCandidatesByText("你好");
		//for (TermInfo termInfo : tInfos) {
		//	System.out.println(termInfo.term.text());
		//}
		//mysqlHelper.insert("test", 23, (float)0.3, (float)3.3, 22);
	  //  mysqlHelper.clearDB();
		
	}
}
